package com.eam.handler.facilities;

import com.basis.handler.service.DataImportInterface;
import com.basis.utils.CodeGenerateRuleUtil;
import com.eam.data.*;
import com.mes.data.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.jettison.json.JSONObject;
import org.openbravo.dal.service.OBDal;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.common.enterprise.Organization;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@SuppressWarnings("unchecked")
public class CheckPlanImportService extends DataImportInterface {
	protected static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");//处理时间格式
	protected String tableName = null;
	protected String clientId = null;
	protected String orgId = null;
	protected String userId = null;
	
	public void setEmesDataImport(EmesDataImport dataImport) {
	}

	public JSONObject importData(InputStream is, String fileName) throws Exception {
		JSONObject result = new JSONObject();
		try {
			Workbook wb = null;
			if (fileName.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileName.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			}
			if (wb != null) {
				result = this.readExcel(wb);
				if (!result.getBoolean("result")) {
					OBDal.getInstance().rollbackAndClose();
					return result;
				} else {
					OBDal.getInstance().commitAndClose();
					result.put("result", true);
					result.put("msg", "导入成功");
					return result;
				}
			} else {
				result.put("msg", "文件格式错误，至于许EXCEL的导入");
				return result;
			}
		} catch (Exception e) {
			e.printStackTrace();
			OBDal.getInstance().rollbackAndClose();
			if (e.getClass().getName().contains("ConstraintViolationException")) {
				result.put("result", false);
				result.put("msg", "文件数据有问题，请仔细检查。");
			}
		} finally {
			is.close();
		}
		return result;
	}
	
	private JSONObject readExcel(Workbook wb) throws Exception {
		String meg = "", errorMsg;
		int number = wb.getNumberOfSheets();
		JSONObject result = new JSONObject();
		result.put("result", true);
		if (number <= 0) {
			result.put("result", false);
			result.put("msg", "文件格式错误。请仔细检查");
			return result;
		} else {
			// 读取第一页
			Sheet sheet = wb.getSheetAt(0);
			int lastRowNum = sheet.getLastRowNum();
			for (int i = 2; i <= lastRowNum; i++) {
				Row row = sheet.getRow(i);
				errorMsg = this.checkData(row);
				if (errorMsg != null && !"".equals(errorMsg)) {
					meg = errorMsg;
				}
				
			}
			if (!"".equals(meg)) {
				result.put("result", false);
				result.put("msg", meg);
			}
			return result;
			
		}
	}
	
	
	/**
	 * 开始存储记录
	 * 
	 * @param row
	 * @return
	 * @throws Exception
	 */
	
	private String checkData(Row row) throws Exception {
		String meg = "";
		try {
			String Personliable = (String)readValue(row.getCell(0));// 计划创建人
			Double  days = (Double)readValue(row.getCell(1));		//点检周期
			String Remarkd= (String)readValue(row.getCell(2));		//说明描述
			String reqipemt = (String)readValue(row.getCell(3));	//点检设备编码
			String usershebei = (String)readValue(row.getCell(4));  //点检设备责任人
			String checkproject = (String)readValue(row.getCell(5)); //点检项目
			String Organizations = (String)readValue(row.getCell(6)); //组织机构
			String status = "CREATE";						//状态为创建
			boolean yes = true;
			
			int i = (int) Math.ceil(days);

			String sql = "select * from ad_user where name = ?";
			User counts = (User) OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(User.ENTITY_NAME).setString(0, Personliable).uniqueResult();
			
			User usershebeis = (User) OBDal.getInstance().getSession().createSQLQuery(sql).addEntity(User.ENTITY_NAME).setString(0, usershebei).uniqueResult();
			
			
			String aql = "select * from Eam_Facilities where code = ? ";
			EamFacilities requipments = (EamFacilities) OBDal.getInstance().getSession().createSQLQuery(aql).addEntity(EamFacilities.ENTITY_NAME).setString(0, reqipemt).uniqueResult();
			
			String wql = " select count(*) from Eam_Check_Item where code = ? ";
			Long bianmas = (Long) OBDal.getInstance().getSession().createSQLQuery(wql).setString(0, checkproject).uniqueResult();
			
			String eql = "select * from Eam_Check_Item where code = ? ";
			EamCheckItem xiangmu = (EamCheckItem)OBDal.getInstance().getSession().createSQLQuery(eql).addEntity(EamCheckItem.ENTITY_NAME).setString(0, checkproject).uniqueResult();
			
			String rql ="select * from emes_process"
					+ " where emes_process_id = (select emes_process_id from emes_process_facilities  "
					+ " where Eam_Facilities_Id = (select Eam_Facilities_Id from Eam_Facilities  where code = ?)) ";
			
			
			EmesProcess processs = (EmesProcess)OBDal.getInstance().getSession().createSQLQuery(rql).addEntity(EmesProcess.ENTITY_NAME).setString(0, reqipemt).uniqueResult(); //查询设备对应的工序
			
			String uql = "select * from ad_org where name = ?";
			
			Organization orgIds  = (Organization)OBDal.getInstance().getSession().createSQLQuery(uql).addEntity(Organization.ENTITY_NAME).setString(0, Organizations).uniqueResult(); //查询组织机构的ID
			
			String  numbers =  "[1-9]{1}[0-9]{0,10}";
			Pattern patterns = Pattern.compile(numbers);
			
			String aa = String.valueOf(i);;
			Matcher matcherq = patterns.matcher(aa);
			boolean as = matcherq.matches();
			
			
			
			if(counts != null){
				if(as){
					if(requipments != null){
						
						if(bianmas != 0){
							if(usershebeis != null){
								if(processs != null){
									
								
								
								EamCheckPlan checkplan = new EamCheckPlan();	//点检计划
								
								EamCheckPlanFacilities checkequipment = new EamCheckPlanFacilities();	//点检设备
								
								EamCheckPlanItem checkdetails = new EamCheckPlanItem(); //点检项目
								
								String code = CodeGenerateRuleUtil.getCode("Eam_Check_Plan", "code");
								
								//将当前系统时间加上点检周期得知下次点检时间
								int clycls= Integer.valueOf(aa);//(int)Math.pow(days, 0);
								Calendar ca = Calendar.getInstance();
								
								ca.add(Calendar.DATE, clycls);// clycls为增加的天数，可以改变的（点检周期）
								 
								Date d = new Date();
								 
								d = ca.getTime();
								 
								Date enddate = sdf.parse(sdf.format(d));  //增加了点检周期后的时间
								
								BigDecimal day=new BigDecimal(days);
								
								//存点检计划
								checkplan.setCode(code);
								checkplan.setDescription(Remarkd);
								checkplan.setCycle(day);
								checkplan.setOrganization(orgIds);
								checkplan.setNextCycleTime(enddate);
								checkplan.setState(status);
								checkplan.setUser(counts);
								OBDal.getInstance().save(checkplan);
								String idtime = checkplan.getId();
								OBDal.getInstance().flush();
								
								String pql ="select * from Eam_Check_Plan where Eam_Check_Plan_Id = ? ";
								
								EamCheckPlan EamFacilities = (EamCheckPlan)OBDal.getInstance().getSession().createSQLQuery(pql).addEntity(EamCheckPlan.ENTITY_NAME).setString(0, idtime).uniqueResult();

								checkequipment.setEamCheckPlan(EamFacilities); //存点检计划的ID
								
								checkequipment.setEamFacilities(requipments);
								checkequipment.setUser(usershebeis);
								checkequipment.setStatus(status); 
								checkequipment.setEmesProcess(processs);
								checkequipment.setActive(yes);
								OBDal.getInstance().save(checkequipment);
								String itemId = checkequipment.getId();
								OBDal.getInstance().flush();
								
								String qql ="select * from Eam_Check_Plan_Facilities where Eam_Check_Plan_Facilities_id = ? ";
								
								EamCheckPlanFacilities runitem = (EamCheckPlanFacilities)OBDal.getInstance().getSession().createSQLQuery(qql).addEntity(EamCheckPlanFacilities.ENTITY_NAME).setString(0, itemId).uniqueResult();
								
								//存点检项目
								checkdetails.setEamCheckPlanFacilities(runitem);	//存点检设备的ID
								checkdetails.setEamCheckItem(xiangmu);
								checkdetails.setStatus(status);
								OBDal.getInstance().save(checkdetails);
								}else{
									meg = "【第" + (row.getRowNum() + 1) + "行,点检设备没有对应的工序！！";
								}
							}else{
								meg = "【第" + (row.getRowNum() + 1) + "行,点检设备责任人不存在！！";
							}
							
						}else{
							meg = "【第" + (row.getRowNum() + 1) + "行,点检项目编码不存在！！";
						}
					}else{
						meg = "【第" + (row.getRowNum() + 1) + "行,点检设备编码不存在！！";
					}						
				}else{
					meg = "【第" + (row.getRowNum() + 1) + "行,点检周期格式不对！！";
				}
			}else{
				meg = "【第" + (row.getRowNum() + 1) + "行,计划创建人不存在！！";
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return meg;
		
	}
	
	/**
	 * 读取单元格数据
	 */
	protected Object readValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		Double cellValue = null;
		if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}
			cellValue = cell.getNumericCellValue();
			return cellValue.longValue() < cellValue ? cellValue : cellValue.longValue();
		} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			return cell.getStringCellValue().trim();
		} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
			throw new RuntimeException("第" + (cell.getRowIndex() + 1) + "行，" + (cell.getColumnIndex() + 1) + "列，数据格式有问题,请检查");
		} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else {
			return "";
		}
	}
}